package Database;

import java.sql.*;
import java.util.Calendar;

      
public class CreateTables
{ 
	private Connection conn = null;
	private PreparedStatement stmt = null;
	private DBConnection db;
	private String[] tables;
    
    public CreateTables()
    {
    	tables = new String[12];
    	db = new DBConnection();
		conn = db.openDB();
    }	
	public void createTables()
	{
		try
			{
				//sequences
		    	//users
		        String drop = "drop sequence staff_id";
		        stmt = conn.prepareStatement(drop);
		        stmt.executeUpdate();
		        String seq = "create sequence staff_id start with 1 increment by 1";
		        stmt = conn.prepareStatement(seq);
		        stmt.executeUpdate();
		        
		        //product
		        drop = "drop sequence prod_id";
		        stmt = conn.prepareStatement(drop);
		        stmt.executeUpdate();
		        seq = "create sequence prod_id start with 1 increment by 1";
		        stmt = conn.prepareStatement(seq);
		        stmt.executeUpdate();
		        
		        //transaction
		        drop = "drop sequence trans_id";
		        stmt = conn.prepareStatement(drop);
		        stmt.executeUpdate();
		        seq = "create sequence trans_id start with 1 increment by 1";
		        stmt = conn.prepareStatement(seq);
		        stmt.executeUpdate();
		        
		        //supplier
		        drop = "drop sequence supp_id";
		        stmt = conn.prepareStatement(drop);
		        stmt.executeUpdate();
		        seq = "create sequence supp_id";
		        stmt = conn.prepareStatement(seq);
		        stmt.executeUpdate();
		        
		        //customer
		        drop = "drop sequence card_id";
		        stmt = conn.prepareStatement(drop);
		        stmt.executeUpdate();
		        seq = "create sequence card_id start with 1 increment by 1";
		        stmt = conn.prepareStatement(seq);
		        stmt.executeUpdate();
					
				//tables
				drop = "drop table Food";
				stmt = conn.prepareStatement(drop);
				stmt.executeUpdate();
				drop = "drop table ProductTransaction";
				stmt = conn.prepareStatement(drop);
				stmt.executeUpdate();
				drop = "drop table Alcoholic";
				stmt = conn.prepareStatement(drop);
				stmt.executeUpdate();
				drop = "drop table CleaningProduct";
				stmt = conn.prepareStatement(drop);
				stmt.executeUpdate();
				drop = "drop table Drink";
				stmt = conn.prepareStatement(drop);
				stmt.executeUpdate();
				drop = "drop table Transactions";
				stmt = conn.prepareStatement(drop);
				stmt.executeUpdate();
				drop = "drop table Product";
				stmt = conn.prepareStatement(drop);
				stmt.executeUpdate();
				drop = "drop table Manager";
				stmt = conn.prepareStatement(drop);
				stmt.executeUpdate();
				drop = "drop table SalesAssistant";
				stmt = conn.prepareStatement(drop);
				stmt.executeUpdate();
				drop = "drop table Users";
				stmt = conn.prepareStatement(drop);
				stmt.executeUpdate();
				drop = "drop table Supplier";
				stmt = conn.prepareStatement(drop);
				stmt.executeUpdate();
				drop = "drop table LoyaltyCustomer";
				stmt = conn.prepareStatement(drop);
				stmt.executeUpdate();
				System.out.println("Tables dropped");
				
	    		
				tables[0] = "create table Users " + "(staffID number not null, username varchar2(20), fname char(15), lname char(15), password varchar2(20), primary key (staffID))";
				tables[1] = "create table Manager " + "(staffID number not null, primary key (staffID), foreign key (staffID) references Users on delete cascade)";
				tables[2] = "create table SalesAssistant " + "(staffID number not null, primary key (staffID), foreign key (staffID) references Users on delete cascade)";
				tables[3] = "create table LoyaltyCustomer " + "(cardID number not null, c_name char(20), address varchar2(40), phone varchar2(15), " +
						"email varchar2(20), points number, startDate date, primary key (cardID))";
				tables[4] = "create table Transactions " + "(transactionID number not null, trans_date date,total_cost float, total_points number, itemQuantity number, cardID number not null, staffID number not null, " +
						"primary key (transactionID), foreign key (cardID) references LoyaltyCustomer on delete cascade, foreign key (staffID) references Users on delete cascade)";
				tables[5] = "create table Supplier " + "(supplierID number not null, name char(20), primary key (supplierID))";
				tables[6] = "create table Product " + "(productID number not null, productName varchar2(30), price float, inDate date,reorder_level number, quantity number, cost float, reorder_amount number,supplierID number not null, " +
						"primary key (productID), foreign key (supplierID) references Supplier on delete cascade)"; 
				tables[7] = "create table ProductTransaction " + "(transactionID number not null, productID number not null, quantity number, price float, primary key (transactionID, productID), " +
						"foreign key (transactionID) references transactions on delete cascade, foreign key (productID) references product on delete cascade)";
				tables[8] = "create table Food " + "(productID number not null,calories number, primary key (productID), foreign key (productID) references Product on delete cascade)";
				tables[9] = "create table Drink " + "(productID number not null, bottleSize number, primary key (productID), foreign key (productID) references Product on delete cascade)";
				tables[10] = "create table CleaningProduct " + "(productID number not null, toxic number check(toxic < 2),  primary key (productID), foreign key (productID) references Product on delete cascade)";				
				tables[11] = "create table Alcoholic " + "(productID number not null, alcoholContent number,  primary key (productID), foreign key (productID) references Product on delete cascade)";
	    		
				for(int i = 0; i < tables.length; i++)
				{
					stmt = conn.prepareStatement(tables[i]);
					stmt.executeUpdate();
				}
				System.out.println("tables created");
				conn.commit();
			}	    
			catch (SQLException e)
			{
				System.out.print("SQL Exception " + e);
				System.exit(1);
			}	
	}
	
	public void insertValues()
	{
		try
		{
			//inserting default staff for demo purposes
			String insert = "insert into Users(staffID, username, fname, lname, password) values(staff_id.nextval,?,?,?,?)";
			stmt = conn.prepareStatement(insert);
			stmt.setString(1, "admin");
			stmt.setString(2, "Alex");
			stmt.setString(3, "Mercer");
			stmt.setString(4, "prototype");
			stmt.executeUpdate();
			
			stmt.setString(1, "sa");
			stmt.setString(2, "James");
			stmt.setString(3, "Heller");
			stmt.setString(4, "armydude");
			stmt.executeUpdate();
			
			stmt.setString(1, "sa2");
			stmt.setString(2, "Joe");
			stmt.setString(3, "Bloggs");
			stmt.setString(4, "randomdude");
			stmt.executeUpdate();
			
			stmt.setString(1, "sa3");
			stmt.setString(2, "Anders");
			stmt.setString(3, "Andorhal");
			stmt.setString(4, "dragonage");
			stmt.executeUpdate();
			
			stmt.setString(1, "admin2");
			stmt.setString(2, "Adrean");
			stmt.setString(3, "Person");
			stmt.setString(4, "randomguy");
			stmt.executeUpdate();
			
			insert = "insert into manager(staffID) values(?)";
			stmt = conn.prepareStatement(insert);
			stmt.setInt(1, 1);
			stmt.executeUpdate();
			
			insert = "insert into SalesAssistant(staffID) values(?)";
			stmt = conn.prepareStatement(insert);
			stmt.setInt(1, 2);
			stmt.executeUpdate();
			
			stmt.setInt(1, 3);
			stmt.executeUpdate();
			
			//inserting default supplier for demo purposes
			insert = "insert into Supplier(SupplierID, name) values(supp_id.nextval,?)";
			stmt = conn.prepareStatement(insert);
			stmt.setString(1, "Rum Sellers Ltd");
			stmt.executeUpdate();
			
			stmt.setString(1, "Bakers Ltd");
			stmt.executeUpdate();
			
			stmt.setString(1, "Soft Drinks Ltd");
			stmt.executeUpdate();
			
			stmt.setString(1, "Supply the Clean Ltd");
			stmt.executeUpdate();
			
			stmt.setString(1, "Daily Needs Ltd");
			stmt.executeUpdate();
			
			//inserting default products for demo purposes
			java.util.Date utilDate = new java.util.Date();
		    java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
		    sqlDate.setDate(22);
			insert = "insert into Product(productID, productname,price,inDate,reorder_level,quantity,cost,reorder_amount,SupplierID) values(prod_id.nextval,?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(insert);
			stmt.setString(1, "Rum");
			stmt.setDouble(2, 25);
			stmt.setDate(3, sqlDate);
			stmt.setInt(4, 10);
			stmt.setInt(5, 20);
			stmt.setDouble(6, 17.5);
			stmt.setInt(7, 20);
			stmt.setInt(8, 1);
			stmt.executeUpdate();
			
			insert = "insert into drink(productID,bottlesize) values(prod_id.currval,?)";
			stmt = conn.prepareStatement(insert);
			stmt.setInt(1, 1000);
			stmt.executeUpdate();
			
			insert = "insert into alcoholic(productID,alcoholcontent) values(prod_id.currval,?)";
			stmt = conn.prepareStatement(insert);
			stmt.setDouble(1, 35);
			stmt.executeUpdate();
			
			insert = "insert into Product(productID, productname,price,inDate,reorder_level,quantity,cost,reorder_amount,SupplierID) values(prod_id.nextval,?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(insert);
			stmt.setString(1, "Pepsi");
			stmt.setDouble(2, 1.4);
			stmt.setDate(3, sqlDate);
			stmt.setInt(4, 20);
			stmt.setInt(5, 30);
			stmt.setDouble(6, .75);
			stmt.setInt(7, 25);
			stmt.setInt(8, 1);
			stmt.executeUpdate();
			
			insert = "insert into drink(productID,bottlesize) values(prod_id.currval,?)";
			stmt = conn.prepareStatement(insert);
			stmt.setInt(1, 500);
			stmt.executeUpdate();

			insert = "insert into Product(productID, productname,price,inDate,reorder_level,quantity,cost,reorder_amount,SupplierID) values(prod_id.nextval,?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(insert);
			sqlDate = new java.sql.Date(utilDate.getTime());
			stmt.setString(1, "Bread");
			stmt.setDouble(2, 2);
			stmt.setDate(3, sqlDate);
			stmt.setInt(4, 30);
			stmt.setInt(5, 50);
			stmt.setDouble(6, 1);
			stmt.setInt(7, 30);
			stmt.setInt(8, 2);
			stmt.executeUpdate();									
			
			insert = "insert into food(productID,calories) values(prod_id.currval,?)";
			stmt = conn.prepareStatement(insert);
			stmt.setInt(1, 200);
			stmt.executeUpdate();
			
			insert = "insert into Product(productID, productname,price,inDate,reorder_level,quantity,cost,reorder_amount,SupplierID) values(prod_id.nextval,?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(insert);
			sqlDate = new java.sql.Date(utilDate.getTime());
			stmt.setString(1, "Bleach");
			stmt.setDouble(2, 5.5);
			stmt.setDate(3, sqlDate);
			stmt.setInt(4, 10);
			stmt.setInt(5, 20);
			stmt.setDouble(6, 3.75);
			stmt.setInt(7, 15);
			stmt.setInt(8, 4);
			stmt.executeUpdate();
			
			insert = "insert into cleaningproduct(productID,toxic) values(prod_id.currval,?)";
			stmt = conn.prepareStatement(insert);
			stmt.setInt(1, 1);
			stmt.executeUpdate();

			//add
			insert = "insert into Product(productID, productname,price,inDate,reorder_level,quantity,cost,reorder_amount,SupplierID) values(prod_id.nextval,?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(insert);
			sqlDate = new java.sql.Date(utilDate.getTime());
			stmt.setString(1, "Milk");
			stmt.setDouble(2, 2);
			stmt.setDate(3, sqlDate);
			stmt.setInt(4, 20);
			stmt.setInt(5, 30);
			stmt.setDouble(6, 1.25);
			stmt.setInt(7, 15);
			stmt.setInt(8, 5);
			stmt.executeUpdate();
			
			insert = "insert into Drink(productID,bottlesize) values(prod_id.currval,?)";
			stmt = conn.prepareStatement(insert);
			stmt.setInt(1, 2000);
			stmt.executeUpdate();
			
			insert = "insert into Product(productID, productname,price,inDate,reorder_level,quantity,cost,reorder_amount,SupplierID) values(prod_id.nextval,?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(insert);
			sqlDate = new java.sql.Date(utilDate.getTime());
			stmt.setString(1, "Eggs");
			stmt.setDouble(2, 1.5);
			stmt.setDate(3, sqlDate);
			stmt.setInt(4, 15);
			stmt.setInt(5, 20);
			stmt.setDouble(6, .5);
			stmt.setInt(7, 20);
			stmt.setInt(8, 5);
			stmt.executeUpdate();
			
			insert = "insert into food(productid,calories) values(prod_id.currval,?)";
			stmt = conn.prepareStatement(insert);
			stmt.setInt(1, 150);
			stmt.executeUpdate();
			
			insert = "insert into Product(productID, productname,price,inDate,reorder_level,quantity,cost,reorder_amount,SupplierID) values(prod_id.nextval,?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(insert);
			sqlDate = new java.sql.Date(utilDate.getTime());
			stmt.setString(1, "Sugar");
			stmt.setDouble(2, 2);
			stmt.setDate(3, sqlDate);
			stmt.setInt(4, 30);
			stmt.setInt(5, 40);
			stmt.setDouble(6, 1);
			stmt.setInt(7, 40);
			stmt.setInt(8, 5);
			stmt.executeUpdate();
			
			insert = "insert into food(productid,calories) values(prod_id.currval,?)";
			stmt = conn.prepareStatement(insert);
			stmt.setInt(1, 10);
			stmt.executeUpdate();
			
			insert = "insert into Product(productID, productname,price,inDate,reorder_level,quantity,cost,reorder_amount,SupplierID) values(prod_id.nextval,?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(insert);
			sqlDate = new java.sql.Date(utilDate.getTime());
			stmt.setString(1, "Chocolate");
			stmt.setDouble(2, .85);
			stmt.setDate(3, sqlDate);
			stmt.setInt(4, 50);
			stmt.setInt(5, 80);
			stmt.setDouble(6, .25);
			stmt.setInt(7, 60);
			stmt.setInt(8, 5);
			stmt.executeUpdate();
			
			insert = "insert into food(productid,calories) values(prod_id.currval,?)";
			stmt = conn.prepareStatement(insert);
			stmt.setInt(1, 300);
			stmt.executeUpdate();
			
			//this serves as the businesses purchases card id
			insert = "insert into LoyaltyCustomer(cardID,c_name,address,phone,email,points,startdate) values(?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(insert);
			stmt.setInt(1, -1);
			stmt.setString(2, "Store Purchases");
			stmt.setString(3, null);
			stmt.setString(4, null);
			stmt.setString(5, null);
			stmt.setInt(6, 0);
			stmt.setDate(7, null);
			stmt.executeUpdate();
			
			stmt.setInt(1, 0);
			stmt.setString(2, "Public");
			stmt.setString(3, null);
			stmt.setString(4, null);
			stmt.setString(5, null);
			stmt.setInt(6, 0);
			stmt.setDate(7, null);
			stmt.executeUpdate();
			
			//inserting customers for demo purposes
			insert = "insert into LoyaltyCustomer(cardID,c_name,address,phone,email,points,startdate) values(card_id.nextval,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(insert);			
			
			stmt.setString(1, "Lisa Towell");
			stmt.setString(2, "18 The Grange");
			stmt.setString(3, "0871234567");
			stmt.setString(4, "LisaTowell@email.com");
			stmt.setInt(5, 100);
			stmt.setDate(6, sqlDate);
			stmt.executeUpdate();
			
			stmt.close();
		}
		catch(SQLException e)
		{
			e.getMessage();
		}
		
		db.closeDB();
	}


	public static void main(String args[])
	{
		CreateTables ct = new CreateTables();
		ct.createTables();
		ct.insertValues();
	}
}